#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# execl 单元格合并 
import os 
import pandas as pd
import numpy as np  
 
from openpyxl import load_workbook
from openpyxl.styles import Alignment
  

#合并字段单元格
def merging(file,sheet,segvars):
  selected=segvars
  df=pd.read_excel(file,sheet)
  wb=load_workbook(file)
  ws=wb[sheet]
  mergecells(ws,df,selected)
  try:
    wb.save(file)
    print('提示',file+'-'+sheet+'指定单元格合并完成')
    os.system('start '+os.path.dirname(file))
  except Exception as e:
    print('警告',str(e))
  
 
def mergecells(ws,df,cols):
  col=cols[0]
  gdic=df.groupby(col).groups 
  aligncenter=Alignment(horizontal='center',vertical='center')
  for gname in gdic:
    indexs=gdic[gname]+2
    indexs=indexs.sort_values()
    for col in cols:		#每一个要合并的字段
      colindex=df.columns.tolist().index(col)+1
      ws.merge_cells(start_row=indexs[0],end_row=indexs[-1],start_column=colindex,end_column=colindex)	#合并
      for i in range(1,ws.max_row+1):		#实现居中
        ws.cell(row=i,column=colindex).alignment=aligncenter
 

# CODE	单位	职级	部门	性别
wbfile=r"E:\01-work\014-python\data\test.xlsx"
sheet='Sheet1' 
segvars=['CODE','单位','职级','部门','性别']
merging(wbfile,sheet,segvars)